﻿
/*
 * 样品参数目前也采用Excel文件上传-->解析 的方式。 2009-4-12 SZJ 根据QMW的反馈修改；
增加了字段：Ref_Par_File  参数文件
Name	Code	Data Type	Length	Precision	Primary	Foreign Key	Mandatory
参数文件	Ref_Par_File	nvarchar(255)	255		FALSE	FALSE	FALSE
2009-4-10
 * 
 * */
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using EQT.Model;

namespace EQT.Dal
{
    /// <summary>
    /// 样品参数
    /// </summary>
    public partial class DaSampleParameter   
    {
        /// <summary>
        /// 添加参数文件，参数实体中，包含了引用文件字段Ref_Par_File
        /// </summary>
        /// <param name="motf"></param>
        /// <param name="mosp"></param>
        /// <returns></returns>
        public int AddSampleParameterFile(MoTfiles motf, MoSampleParameter mosp)
        {
            int rc = 0;
            DaTfiles datf = new DaTfiles(db);           
            DbTransaction trans = null;
            using (DbConnection conn = db.CreateConnection())
            {
                try
                {
                    conn.Open();
                    using (trans = conn.BeginTransaction())
                    {
                        rc+=datf.Add(motf, trans);  //文件信息     
                        rc+=Add(mosp, trans);       //样品参数                                      
                        trans.Commit();
                    }

                    conn.Close();
                }
                catch (DbException de)
                {
                    if (trans != null) trans.Rollback();
                    rc = 0;
                    throw new DalException("添加参数文件失败！" + motf.ToString() + "\r\n" + mosp.ToString(), de);
                }
            }
            return rc;

        }

        internal const string SQL_GET_SAMPLE_PARAMETER_FILE_INFO_FMT = "SELECT SAMPLE_ID ,REPORT_ID ,REF_PAR_FILE,File_Name ,File_Create_Time FROM SAMPLE_PARAMETER INNER JOIN TFiles ON File_ID=REF_PAR_FILE WHERE REPORT_ID='{0}' ";


        /// <summary>
        /// 返回报告关联的所有参数文件。从SAMPLE_PARAMETER中获取。
        /// 其中Ref_Par_File存储的是文件ID。
        /// </summary>
        /// <param name="report_id"></param>
        /// <returns></returns>
        public DataSet GetSampleParameterFileInfo(string report_id)
        {
            string sql = String.Format(SQL_GET_SAMPLE_PARAMETER_FILE_INFO_FMT, report_id);
            return db.ExecuteDataSet(CommandType.Text, sql);
        }


        //查询所有被成功解析的参数文件的文件ID
        internal const string SQL_GET_PARSED_PAR_FILE = "SELECT REF_PAR_FILE FROM SAMPLE_PARAMETER WHERE REPORT_ID=@REPORT_ID AND EXISTS (SELECT FILE_ID FROM  ExcelSrvResult WHERE FILE_ID=REF_PAR_FILE)";

        /// <summary>
        /// 给定报告ID，返回某个报告的参数文件摘要信息。
        /// dbo.usp_ExcelMarkValueBrief
        /// </summary>
        /// <param name="report_id"></param>
        /// <returns></returns>
        public string GetParametersSumary(string report_id)
        {
            System.Text.StringBuilder sb = new StringBuilder();

            DbCommand cmd = db.GetStoredProcCommand("dbo.usp_ExcelMarkValueBrief");
            db.AddInParameter(cmd, "file_id", DbType.String);
            db.AddOutParameter(cmd, "Comments", DbType.String, 1000);

            DbCommand cmd_files = db.GetSqlStringCommand(SQL_GET_PARSED_PAR_FILE);
            db.AddInParameter(cmd_files, "REPORT_ID", DbType.String, report_id);
            using (IDataReader reader = db.ExecuteReader(cmd_files))
            {
                while (reader.Read())
                {
                    string file_id = reader.GetString(0);
                    db.SetParameterValue(cmd, "file_id", file_id);
                    db.ExecuteNonQuery(cmd);
                    object rc= db.GetParameterValue(cmd, "Comments");
                    if (rc != null && rc != DBNull.Value)
                    {
                        sb.Append((string)rc);
                    }

                }
            }
            if (sb.Length > 200)
            {
                sb.Length = 200;
            }
            else
            {
                sb.Append("<<无参数摘要信息：可能没有上传参数文件,或者服务器未能处理此参数文件.>>");
            }
            return sb.ToString();

        }

    }
}

